<?php
/**
 * Created by JetBrains PhpStorm.
 * User: pete
 * Date: 10/29/13
 * Time: 1:13 AM
 * To change this template use File | Settings | File Templates.
 */

class FMIS_Committee_Model extends CI_Model{

    public function get_committee()
    {
        $sql = "
        SELECT exp_fmis_committee.ID,
        CONCAT(FirstName,' ', LastName)
        as 'Name', Description
        FROM exp_fmis_member_profile
        INNER JOIN ((exp_fmis_committee
        INNER JOIN exp_fmis_committee_member
        ON exp_fmis_committee.ID = CommitteeID)
        INNER JOIN exp_fmis_position
        ON Position = exp_fmis_position.ID)
        ON exp_fmis_member_profile.MemberID = exp_fmis_committee_member.MemberID
        Order By exp_fmis_committee.ID desc
        ";

        $query = $this->db->query($sql);
        return $query->result_array();
    }

    public function updateCommittee($data)
    {
        $areahead = $data['areahead'];
        $assistant = $data['assistant'];
        $secretary = $data['secretary'];
        $treasurer = $data['treasurer'];
        //$array_id_list = $data['array_id_list'];

        $sqlCleanCommittee = "
            DELETE FROM exp_fmis_committee_member
            WHERE CommitteeID = 1;
        ";
        $this->db->query($sqlCleanCommittee);

        $sqlAreaHead = "
        INSERT INTO fmis.exp_fmis_committee_member (CommitteeID, MemberID, Position)
        VALUES (?, ?, ?)
        ";

        $this->db->query($sqlAreaHead, array(
            1,
            $areahead,
            1
        ));

        $sqlAssistant = "
        INSERT INTO fmis.exp_fmis_committee_member (CommitteeID, MemberID, Position)
        VALUES (?, ?, ?)
        ";

        $this->db->query($sqlAssistant, array(
            1,
            $assistant,
            2
        ));

        $sqlSecretary = "
        INSERT INTO fmis.exp_fmis_committee_member (CommitteeID, MemberID, Position)
        VALUES (?, ?, ?)
        ";

        $this->db->query($sqlSecretary, array(
            1,
            $secretary,
            3
        ));

        $sqlTreasurer = "
        INSERT INTO fmis.exp_fmis_committee_member (CommitteeID, MemberID, Position)
        VALUES (?, ?, ?)
        ";

        $this->db->query($sqlTreasurer, array(
            1,
            $treasurer,
            4
        ));

        $sqlSelectMembers = "
        SELECT ID as 'ID'
         FROM exp_fmis_member
         WHERE ID <> ? AND ID <> ? AND ID <> ? AND ID <> ?
        ";
        $id_list = $this->db->query($sqlSelectMembers, array(
            $areahead, $assistant, $treasurer, $secretary
        ))->result_array();
        foreach($id_list as $member)
        {
            $sqlMember = "
            INSERT INTO fmis.exp_fmis_committee_member (CommitteeID, MemberID, Position)
            VALUES (?, ?, ?)
            ";
            $this->db->query($sqlMember, array(
                1,
                $member['ID'],
                5
            ));
        }
        //TODO update exp_fmis_committee_member
    }
}
